﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Data;


namespace ArchSoft
{
    class ExcelStore : IStateMonitor
    {
        Application oXL;
        static int HeadeLine = 5;
        static int Stt = 1;
        string PageSize = "A2";
        _Workbook oWB;
        _Worksheet oSheet;

        public ExcelStore() //static
        {
            oXL = new Application();
            oXL.Caption = Guid.NewGuid().ToString();
            oXL.Visible = false;
            oXL.UserControl = false;
            string ver = oXL.Version;
        }

        [DllImport("user32.dll")]
        static extern int EndTask(IntPtr hWnd);

        [DllImport("user32.dll")]
        static extern IntPtr FindWindow(string lpClassName, string lpWindowName);

        [DllImport("user32.dll")]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

        [DllImport("kernel32.dll")]
        static extern IntPtr SetLastError(int dwErrCode);

        //private void LoadExcelFile()
        //{
        //       try
        //    {
        //        oWB = oXL.Workbooks.Open(_fullPath,
        //                                           0,
        //                                           false,
        //                                           5,
        //                                           "",
        //                                           "",
        //                                           false,
        //                                           XlPlatform.xlWindows,
        //                                           "\t",
        //                                           true,
        //                                           false,
        //                                           0,
        //                                           true,
        //                                           1,
        //                                           0);
        //        oSheet = (Worksheet)oWB.ActiveSheet;
        //        //Get a new workbook.
        //        //oWB = (_Workbook)(oXL.Workbooks.
        //        //oSheet = (_Worksheet)oWB.ActiveSheet;
        //        //oSheet.Rows.RowHeight = 15;
        //    }
        //    catch (Exception theException)
        //    {
        //        String errorMessage;
        //        errorMessage = "Error: ";
        //        errorMessage = String.Concat(errorMessage, theException.Message);
        //        errorMessage = String.Concat(errorMessage, " Line: ");
        //        errorMessage = String.Concat(errorMessage, theException.Source);

        //        Console.WriteLine(errorMessage);
        //        Console.ReadLine();
        //    }

        //}

        string GetTitle()
        {
            string Pat = Directory.GetParent(this._folderPath).ToString() + Path.DirectorySeparatorChar + ProjectManager.PROJECT_INF_FILE;
            if (File.Exists(Pat))
            {
                //Load Noi dung File Dinh nghia(neu ton tai)!!!               
                System.Data.DataTable tbConsl = null;

                DataSet ds = new DataSet();
                ds.ReadXml(Pat);
                tbConsl = ds.Tables[0];
                //Load Consultant!
                if (tbConsl != null)
                {
                    //Gene!!    .TrimEnd(new char[] { '\0' });
                    Pat = EncodeDecode.GetDecoded(tbConsl.Rows[5][1].ToString()).Trim();
                    PageSize = EncodeDecode.GetDecoded(tbConsl.Rows[20][1].ToString()).Trim().Split(' ')[0];
                    if (Pat == "")
                        Pat = "NO NAME";
                }
                else
                    Pat = "NO NAME";
            }
            else
            {
                Pat = "NO NAME";
            }
            return Pat;
        }        

        private void NewExcelFile()
        {
            try
            {
                //Get a new workbook.
                oWB = (_Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (_Worksheet)oWB.ActiveSheet;
                oSheet.Rows.RowHeight = 15;

                //Format A1:D1 as bold, vertical alignment = center.
                Range range;                

                //Ten Cong Trinh! Lay tu phan Descript cua Project Info
                range = oSheet.get_Range("A1", "F1");
                range.EntireRow.RowHeight= 25;
                range.Merge(true);
                range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Bold = true;
                range.Font.Size = 13;
                range.Value2 = GetTitle();
                range.EntireColumn.AutoFit();

                //Main Title
                range = oSheet.get_Range("A2", "F2");
                range.EntireRow.RowHeight = 40;
                range.Merge(true);
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Bold = true;
                range.Font.Size = 18;
                range.Value2 = "THỐNG KÊ BẢN VẼ THIẾT KẾ KỸ THUẬT";
                range.EntireColumn.AutoFit();

                //Vice Title
                range = oSheet.get_Range("A3", "F3");
                range.EntireRow.RowHeight = 20;
                range.Merge(true);
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Font.Bold = true;
                range.Font.Size = 14;
                range.Value2 = ProjectManager.PROJECT_EXCEL_STITLE;
                range.EntireColumn.AutoFit();

                //Column Header[STT]
                range = oSheet.get_Range("A5", "A5");
                range.EntireColumn.ColumnWidth = 5;
                range.Font.Size = 10;
                range.EntireRow.RowHeight = 20;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Cells[1, 1] = "STT";
                FormatBorder(range);
                
                //Column Header[Ma Ban Ve]
                range = oSheet.get_Range("B5", "B5");
                range.EntireColumn.ColumnWidth = 13;
                range.Font.Size = 10;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Cells[1, 1] = "MÃ BẢN VẼ";
                FormatBorder(range);
                
                //Column Header[So Ban Ve]
                range = oSheet.get_Range("C5", "C5");
                range.EntireColumn.ColumnWidth = 13;
                range.Font.Size = 10;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Cells[1, 1] = "SỐ BẢN VẼ";
                FormatBorder(range);

                //Column Header[Ten Ban VE]
                range = oSheet.get_Range("D5", "D5");
                range.EntireColumn.ColumnWidth = 33;
                range.Font.Size = 10;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Cells[1, 1] = "TÊN BẢN VẼ";
                FormatBorder(range);

                //Column Header[Ti le]
                range = oSheet.get_Range("E5", "E5");
                range.EntireColumn.ColumnWidth = 11;
                range.Font.Size = 10;
                range.Font.Bold = true;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.Cells[1, 1] = "TỈ LỆ";
                FormatBorder(range);

                //Column Header[Kho Giay]
                range = oSheet.get_Range("F5", "F5");
                range.EntireColumn.ColumnWidth = 11;
                range.Font.Size = 9;
                range.Font.Bold = true;             
                range.VerticalAlignment = XlVAlign.xlVAlignCenter;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Cells[1, 1] = "KHỔ GIẤY";
                range.EntireRow.AutoFit();
                FormatBorder(range);
                //Column Header[All]
                range = oSheet.get_Range("A5", "F5");
                range.EntireRow.RowHeight = 25;              
                FormatRangeBorder(range, XlBordersIndex.xlEdgeBottom, XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, 9);
                FormatRangeBorder(range, XlBordersIndex.xlEdgeTop, XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, 9);
                oXL.Visible = false;
                oXL.UserControl = false;
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                Console.WriteLine(errorMessage);
                Console.ReadLine();
            }
        }       

        private void FormatRangeBorder(Range range, XlBordersIndex BorderSide, XlLineStyle LStyle, XlBorderWeight BorderWeigh, float Color_)
        {
            range.Borders[BorderSide].Weight = BorderWeigh;
            range.Borders[BorderSide].LineStyle = LStyle;
            range.Borders[BorderSide].Color = Color_;
            range.Borders[BorderSide].ColorIndex = XlColorIndex.xlColorIndexAutomatic;
        }

        private void FormatBorder(Range range)
        {
            range.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
        }


        public void NewLineForSeries(string serieName, int order)
        {
            int row = HeadeLine + order;
            (oSheet.Cells[row, "A"] as Range).EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            Range range = oSheet.get_Range("A" + row.ToString(), "F" + row.ToString());
            range.Clear();
            range.Merge(true);
            range.Font.Bold = true;
            range.Font.Size = 10;
            range.EntireRow.RowHeight = 18;
            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.Value2 = "  " + serieName;           
            FormatRangeBorder(range, XlBordersIndex.xlEdgeBottom, XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, 9);
            FormatRangeBorder(range, XlBordersIndex.xlEdgeTop, XlLineStyle.xlContinuous, XlBorderWeight.xlMedium, 9);
        }

        public void NewLineForDrawing(string DrawingName,string Scale, int order)
        {
            int row = HeadeLine + order;
            (oSheet.Cells[row, "I"] as Range).EntireRow.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);

            string[] names = DrawingName.Split('_');           
            names[1] = Path.GetFileNameWithoutExtension(names[1]);
            Range range = oSheet.get_Range("A" + row.ToString(), "F" + row.ToString());

            range.VerticalAlignment = XlVAlign.xlVAlignCenter;
            range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            FormatRangeBorder(range, XlBordersIndex.xlEdgeBottom, XlLineStyle.xlContinuous, XlBorderWeight.xlThin, 9);
            FormatRangeBorder(range, XlBordersIndex.xlEdgeLeft, XlLineStyle.xlContinuous, XlBorderWeight.xlThin, 9);
            FormatRangeBorder(range, XlBordersIndex.xlEdgeRight, XlLineStyle.xlContinuous, XlBorderWeight.xlThin, 9);
            FormatRangeBorder(range, XlBordersIndex.xlInsideVertical, XlLineStyle.xlContinuous, XlBorderWeight.xlThin, 9);
            FormatCell(range);
            range.Cells[1, 1] = Stt;
            range.Cells[1, 2] = names[0].Split('-')[0];
            range.Cells[1, 3] = names[0].Split('-')[1];
            range.Cells[1, 4] = names[1];
            range.Cells[1, 5] = Scale;            
            range.Cells[1, 6] = PageSize;
            range.EntireRow.RowHeight = 16;
            range = oSheet.get_Range("D" + row.ToString(), "D" + row.ToString());
            range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
            range = oSheet.get_Range("E" + row.ToString(), "E" + row.ToString());
            range.NumberFormat = "h:m";                                   
            Stt += 1;
        }

        private void FormatCell(Range cell)
        {
            cell.Font.Bold = false;
            cell.Font.Size = 8;
        }

        public void DeleteLine(string code, int startIndex, int number)
        {
            if (!ShouldPersist(startIndex))
            {
                return;
            }
            try
            {
                int startRow = HeadeLine + startIndex;
                string codeName = CodeNameUtilities.GetCodeByName(code);
                for (int i = 1; i <= number + 1; i++)
                {
                    if ((oSheet.Cells[i + startRow, "B"] as Range).get_Value(XlRangeValueDataType.xlRangeValueDefault).ToString().StartsWith(codeName))
                    {
                        (oSheet.Cells[i + startRow, "B"] as Range).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
                        break;
                    }
                }

            }
            catch { }
        }

        private string GetNameWithoutCode(string name)
        {
            return Path.GetFileNameWithoutExtension(name.Split('_')[1]);
        }

        private Range FindLineByCode(string code, int startIndex, int length)
        {
            for (int i = 1; i <= length + 1; i++)
            {
                if ((oSheet.Cells[i + startIndex, "B"] as Range).get_Value(XlRangeValueDataType.xlRangeValueDefault).ToString().StartsWith(code))
                {
                    return (oSheet.Cells[i + startIndex, "B"] as Range);
                }
            }
            return null;
        }

        private void RenameLineAsDrawing(string newName, int startIndex, int NumOfChild)
        {
            int startRow = startIndex + HeadeLine;
            string code = CodeNameUtilities.GetCodeByName(newName);
            Range cell = FindLineByCode(code, startRow, NumOfChild);
            if (cell != null)
            {
                Range nameCell = (oSheet.Cells[cell.Row, "C"] as Range);               
                nameCell.Value2 = GetNameWithoutCode(newName);
            }
        }

        #region IStateMonitor Members

        private string _folderPath;
        private string _fullPath;
        public string FolderPath
        {
            set { _folderPath = value; }
        }

        private string projectName;

        public void NewProject(string projectName)
        {
            _fullPath = _folderPath + Path.DirectorySeparatorChar + projectName + ".xls";
            Console.WriteLine("New project at " + _fullPath);
            NewExcelFile();
        }

        public void NewWindow(string Name, int order)
        {
            if (!ShouldPersist(order))
            {
                return;
            }
            if (order == 0)
            {
                return;
            }
            Console.WriteLine("New window at " + order);
            //NewLineForSeries(Name, order);
        }

        public void RenameWindow(string name, int order)
        {
            Console.WriteLine("Rename window at " + order);
            RenameLineAsWindow(name, order);
        }

        private void RenameLineAsWindow(string name, int order)
        {
            int rowIndex = order + HeadeLine;
            Range range = oSheet.get_Range("I" + rowIndex.ToString(), "O" + rowIndex.ToString());

            range.Value2 = name;
            //range.Font.Bold = true;
            //range.Font.Size = 10;
        }

        public void DeleteWindow(int startOrder, int num)
        {
            Console.WriteLine("Delete window at " + startOrder + " num " + num);
            DeleteLineAsWindow(startOrder, num);
        }

        private void DeleteLineAsWindow(int startIndex, int num)
        {
            int startRow = HeadeLine + startIndex;

            for (int i = 0; i < num + 1; i++)
            {
                (oSheet.Cells[startRow, "B"] as Range).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
            }
        }

        public void OpenProject(string projectName)
        {
            _fullPath = _folderPath + Path.DirectorySeparatorChar + projectName + ".xls";
            Console.WriteLine("Open project at " + _fullPath);
            //LoadExcelFile();
            initialzied = false;
        }

        private bool initialzied = true;
        public void FinishOpenProject()
        {
            initialzied = true;
        }



        public void RenameDrawing(int startIndex, int NumOfChild, string newName)
        {
            if (!ShouldPersist(startIndex))
            {
                return;
            }
            string code = CodeNameUtilities.GetCodeByName(newName);
            Console.WriteLine("Rename find from {0} for {1} items with code = {2}. New name = {3}", startIndex, NumOfChild, code, newName);
            RenameLineAsDrawing(newName, startIndex, NumOfChild);
        }


        public void NewDrawing(int startIndex, int NumOfChild, string newFile)
        {
            if (!ShouldPersist(startIndex))
            {
                return;
            }
            Console.WriteLine("New Drawing at line {0}. Name = {1}", startIndex + NumOfChild, newFile);
            //NewLineForDrawing(newFile, startIndex + NumOfChild);
        }

        public void DeleteDrawing(int startIndex, int NumOfChild, string deletedFile)
        {
            string code = CodeNameUtilities.GetCodeByName(deletedFile);
            Console.WriteLine("Delete drawing with code = {0} start from {1}", code, startIndex);
            DeleteLine(deletedFile, startIndex, NumOfChild);
        }

        #endregion

        private bool ShouldPersist(int order)
        {
            return initialzied && (order >= 0);
        }

        public void CloseProject() //static
        {
            if (oXL == null || oWB == null || _fullPath == null)
            {
                return;
            }
            string filePath = _fullPath;
            oXL.UserControl = false;
            if (File.Exists(filePath))
            {
                oWB.Save();
                oWB.Close(false, Missing.Value, false);
            }
            else
            {
                //oXL.UserControl = false;
                //oWB.SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value);
                oWB.Close(true, filePath, false);
            }
            NAR(oSheet);
            NAR(oWB);
        }

        private static void NAR(object o)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
            }
            catch { }
            finally
            {
                o = null;
            }
        }

        public void CloseApplication()
        {

            if (oXL != null)
            {
                EnsureProcessKilled(GetHWnd(), oXL.Caption);
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        private IntPtr GetHWnd() //static
        {
            string ver = oXL.Version;
            if ((int)double.Parse(ver) > 10) return new IntPtr((int)oXL.Parent.Hwnd);
            return new IntPtr(0);
        }

        public static void EnsureProcessKilled(IntPtr MainWindowHandle, string Caption)
        {

            SetLastError(0);

            // for Excel versions <10, this won't be set yet
            if (IntPtr.Equals(MainWindowHandle, IntPtr.Zero))
                MainWindowHandle = FindWindow(null, Caption);

            if (IntPtr.Equals(MainWindowHandle, IntPtr.Zero))
                return; //at this point, presume the window has been closed.

            int iRes, iProcID;
            iRes = GetWindowThreadProcessId(MainWindowHandle, out iProcID);

            if (iProcID == 0) // can’t get Process ID
            {
                if (EndTask(MainWindowHandle) != 0) return; // success
                throw new ApplicationException("Failed to close.");
            }

            System.Diagnostics.Process proc;
            proc = System.Diagnostics.Process.GetProcessById(iProcID);
            proc.CloseMainWindow();
            proc.Refresh();
            if (proc.HasExited) return;
            proc.Kill();
        }
    }
}
